package com.my.jli.util;

import com.my.jli.Service.ExerciseService;
import com.my.jli.entity.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @ClassName ExcelUtil
 * @Description excel文件解析工具
 * @Author MaYu
 * @Date 2022/4/30 21:09
 * @Version 1.0
 **/
public class ExcelUtil {

    public static Map<Exercise, ExerciseOption> getExerciseAndOptionList(File file, ExerciseService exerciseService){
        Map<Exercise, ExerciseOption> map = new HashMap<>();
        List<String> list = getListByExcel(file);
        if (list == null){
            return null;
        }
        for (String exerciseInfo : list) {
            String[] split = exerciseInfo.trim().replace("\n", " ").split(" ");
            //转换成List方便处理
            List<String> infoList = Arrays.asList(split);
            //去除空的部分
            infoList = infoList.stream().filter(s -> !s.isEmpty()).collect(Collectors.toList());
            //只使用格式正确的数据
            if (infoList.size() == 13){
                //获取各个属性
                String typeName = infoList.get(1);
                String title = infoList.get(2);
                String optionA = infoList.get(4);
                String optionB = infoList.get(6);
                String optionC = infoList.get(8);
                String optionD = infoList.get(10);
                String answer = infoList.get(11);
                String chapterName = infoList.get(12);

                ExerciseType type = exerciseService.findTypeByName(typeName);
                ExerciseChapter chapter = exerciseService.findChapterByName(chapterName);

                Exercise exercise = new Exercise();
                exercise.setTitle(title);
                exercise.setAnswer(answer);
                exercise.setChapter(chapter.getId());
                exercise.setType(type.getId());

                ExerciseOption option = new ExerciseOption();
                option.setOptionA(optionA);
                option.setOptionB(optionB);
                option.setOptionC(optionC);
                option.setOptionD(optionD);

                map.put(exercise, option);
            }
        }
        return map;
    }

    public static List<Student> getStudentList(File file, Integer tid){
        List<Student> studentList = new ArrayList<>();
        List<String> list = getListByExcel(file);
        if (list == null){
            return null;
        }
        for (String stuInfo : list) {
            String[] split = stuInfo.split(" ");
            String num = split[0];
            String pwd = split[1];
            String name = split[2];
            Integer sex = Integer.valueOf(split[3]);
            String grade = split[4];
            String clazz = split[5];

            Student student = new Student();
            student.setNum(num);
            student.setPwd(pwd);
            student.setName(name);
            student.setSex(sex);
            student.setGrade(grade);
            student.setClazz(clazz);
            student.setTid(tid);

            studentList.add(student);
        }
        return studentList;
    }

    public static List<String> getListByExcel(File file){
        Workbook wordbook = getWordbook(file);
        List<String> list = new ArrayList<>();
        if (wordbook == null){//如果wordbook对象为空
            return null;
        }
        //开始解析
        Sheet sheet = wordbook.getSheetAt(0);//读取sheet 0
        //第一行是列名，不需要读取
        int firstRowIndex = sheet.getFirstRowNum() + 1;
        int lastRowNum = sheet.getLastRowNum();

        for (int rIndex = firstRowIndex; rIndex <= lastRowNum; rIndex++) {
            Row row = sheet.getRow(rIndex);
            if (row != null){
                StringBuilder oneInfo = new StringBuilder();

                short firstCellNum = row.getFirstCellNum();
                short lastCellNum = row.getLastCellNum();
                for (int cIndex = firstCellNum; cIndex < lastCellNum; cIndex++) {
                    Cell cell = row.getCell(cIndex);
                    if (cell != null){
                        String cellValue = "";
                        //此switch语句是为了防止数字型数据以科学计数法显示
                        switch (cell.getCellTypeEnum()){
                            case STRING:
                                cellValue = cell.getStringCellValue();
                                break;
                            case NUMERIC:
                                cellValue = NumberToTextConverter.toText(cell.getNumericCellValue());
                                break;
                        }

                        if (cIndex == firstCellNum) {
                            oneInfo.append(cellValue);
                        } else {
                            oneInfo.append(" ").append(cellValue);
                        }
                    }
                }
                list.add(oneInfo.toString());
            }
        }
        return list;
    }

    public static Workbook getWordbook(File file){
        if (file.isFile() && file.exists()){//判断文件是否存在
            String[] split = file.getName().split("\\.");//.是特殊字符，需要转义
            Workbook workbook;
            //根据文件后缀判断是xls文件还是xlsx文件
            if ("xls".equals(split[1])){
                try {
                    FileInputStream fileInputStream = new FileInputStream(file);
                    workbook = new HSSFWorkbook(fileInputStream);
                    return workbook;
                } catch (IOException e) {
                    e.printStackTrace();
                }
            } else if ("xlsx".equals(split[1])){
                try {
                    workbook = new XSSFWorkbook(file);
                    return workbook;
                } catch (IOException | InvalidFormatException e) {
                    e.printStackTrace();
                }
            } else {//不是excel文件
                return null;
            }
        } else {//文件不存在
            return null;
        }
        return null;
    }
}
